import numpy as np
import pandas as pd

cater_file_path = '../../data/raw data/餐饮连锁数据.xlsx'
sheet_names = ['门店信息', '菜品信息', '营销记录', '顾客评价']
sheet_name = sheet_names[3]  # 顾客评价表
df_review = pd.read_excel(cater_file_path, sheet_name)

print("\n【空值统计】")
print(df_review.isnull().sum())
print("======================")

# --- 测试1：查看哪些列空值最多 ---
missing_rate = df_review.isnull().mean().sort_values(ascending=False)
print("\n【空值比例（Top 10）】")
print(missing_rate.head(10))
print("======================")

# --- 处理方式：删除含有空值的行 ---
before_rows = df_review.shape[0]
df_review.dropna(inplace=True)
after_rows = df_review.shape[0]
print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")

# --- 验证：是否还有空值 ---
print("\n【空值处理后验证】")
print(df_review.isnull().sum().sum())
print('======================')
print('======================')

# ======================================
# 3️⃣ 检查并处理重复值
# ======================================
print("\n【重复值检测】")
print(df_review.duplicated().sum())
print('================================')

if df_review.duplicated().sum() > 0:
    print(df_review[df_review.duplicated()])

df_review.drop_duplicates(inplace=True)

print("\n【重复值处理后验证】")
print(df_review.duplicated().sum())
print('======================')
print('======================')
print('=============================')

# ======================================
# 4️⃣ 识别并修正异常值
# ======================================

print("\n【数值列统计描述】")
print(df_review.describe())

print('评价日期-----------------------------------------')
outer = '评价日期'
if outer in df_review.columns:
    # 确保评价日期是datetime类型
    df_review[outer] = pd.to_datetime(df_review[outer], errors='coerce')

    # 定义异常值的阈值
    upper = pd.Timestamp('2025-11-02')
    lower = pd.Timestamp('2020-01-01')

    # 标记异常值
    outliers = df_review[(df_review[outer] > upper) | (df_review[outer] < lower) | (df_review[outer].isnull())]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数日期
    median_date = df_review[outer].median()
    df_review.loc[
        (df_review[outer] > upper) | (df_review[outer] < lower) | (df_review[outer].isnull()), outer] = median_date
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数日期 {median_date}。")

print('======================================')
print('======================================')

# ======================================
# 6️⃣ 导出清洗后的数据
# ======================================
clean_path = '../../data/cleared data/餐饮连锁数据_顾客评价2_cleaned.xlsx'
df_review.to_excel(clean_path, index=False)
print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")